import logging
import os
import re
from datetime import timedelta, datetime

import numpy as np
import pandas as pd
from itertools import combinations
from libs.QianMysql import DateEncoder, QianMysql
import pymysql
import sqlite3
from datetime import datetime

from typing import List, Dict, Any, Optional
from collections import defaultdict


MYSQL_HOST = "127.0.0.1"
MYSQL_PORT = 3306
MYSQL_USER = "root"
MYSQL_PASSWORD = "agbwt1412JJH@"
MYSQL_DB = "smart_lng"

# MYSQL_HOST = "123.56.150.119"
# MYSQL_PORT = 33306
# MYSQL_USER = "hesen_23_online"
# MYSQL_PASSWORD = "8.c2K1+'-4jd_Online2023"
# MYSQL_DB = "smart_lng"


# MYSQL_HOST = "123.56.48.97"
# MYSQL_PORT = 3306
# MYSQL_USER = "hesen_23_test"
# MYSQL_PASSWORD = "Bn'\"I0{47.0E_Test2023"
# MYSQL_DB = "smart_lng_before_prod"

# 项目根目录
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
# sql 文件夹路径
SQL_DIR = os.path.join(BASE_DIR, "sql")
# 确保 sql 文件夹存在
os.makedirs(SQL_DIR, exist_ok=True)

# 数据库文件路径
DB_FILE = os.path.join(SQL_DIR, "reconciliation_log.db")


# 获取系统数据
def get_online_data(ids, start_time, end_time):
    db_online = QianMysql(
        {
            "host": MYSQL_HOST,
            "port": MYSQL_PORT,
            "user": MYSQL_USER,
            "passwd": MYSQL_PASSWORD,
            "db": MYSQL_DB,
            "charset": "utf8",
            "autocommit": True,
            "cursorclass": pymysql.cursors.DictCursor,
        }
    )
    return db_online.get_all(
        f"""
        select mgf.id, mgf.station_id , mgf.car_number, round(mgf.gas_num, 2) as gas_num, mgf.create_time, round(mgf.station_cost_price,2) as gas_price,round(mgf.station_cost_money,2) as gas_money, mgf.status, mgf.supplier_reconciliation,
                IFNULL(surr.gas_num,mgf.gas_num) AS actualGasNum,
                mgf.supplier_reconciliation_remark,
               (CASE
                    when ifnull((select CASE WHEN status in (7, 8) THEN 1 ELSE 0 END
                                    from m_gas_fill_abnormal_order
                                    where del_flag = 0
                                    and gas_fill_id = mgf.id
                                    order by create_time desc
                                    limit 1), 1) = 1 and ifnull(mgf.supplier_reconciliation, 0) = 0 then 1
                                    else 0 end)             AS canSubmitExceptionOrder
        FROM m_gas_fill as mgf
        left join s_supplier_reconciliation_rel as surr on surr.m_gas_fill_id = mgf.id and surr.del_flag = 0
        where mgf.create_time >= '{start_time}'
        and mgf.create_time <= '{end_time}'
        and mgf.station_id in ({",".join(map(str, ids))})
        and mgf.pay_status = 1
        and mgf.del_flag = 0
        order by mgf.create_time
    """
    )

def get_online_balance_data(id, start_time, end_time):
    db_online = QianMysql(
        {
            "host": MYSQL_HOST,
            "port": MYSQL_PORT,
            "user": MYSQL_USER,
            "passwd": MYSQL_PASSWORD,
            "db": MYSQL_DB,
            "charset": "utf8",
            "autocommit": True,
            "cursorclass": pymysql.cursors.DictCursor,
        }
    )
    return db_online.get_all(
        f"""
        SELECT 
        s.id, 
        s.`name`, 
        s.abbreviation, 
        IFNULL((SELECT ac.after_change FROM a_account_change ac WHERE ac.account_id = a.id AND ac.create_time <= '{end_time}' ORDER BY ac.create_time DESC, ac.id DESC LIMIT 1), 0) balance, 
        (SELECT IFNULL(sum(ssr.recharge_money), 0) FROM `s_station_recharge` ssr WHERE ssr.del_flag = 0 AND ssr.station_id = s.id AND ssr.examine_type = 1 AND ssr.station_type = 1 AND ssr.payment_time >= '{start_time}' AND ssr.payment_time <= '{end_time}' AND ssr.examine_time > '{end_time}') afterRechargeAmount, 
        (SELECT IFNULL(sum(ssr.recharge_money), 0) FROM `s_station_recharge` ssr WHERE ssr.del_flag = 0 AND ssr.station_id = s.id AND ssr.examine_type = 1 AND ssr.station_type = 1 AND ssr.payment_time < '{start_time}' AND ssr.examine_time > '{end_time}') afterBeforeRechargeAmount
        FROM s_station_subject s 
        LEFT JOIN a_account a ON a.relate_id = s.id AND a.account_type = 7 
        WHERE s.del_flag = 0 AND s.id = '{id}' ORDER BY s.id DESC
    """
    )


# 对比函数
def match_data(df, online_data, station_config):
    # 172800 48小时
    diff_time = 172800 if 'is_time_shuffled' in station_config and station_config['is_time_shuffled'] else 7200
    df = df.copy()
    online_df = pd.DataFrame(online_data)
    # 生成顺序的 result 数组，index 从 0 到 max(df.index)
    max_idx = df.index.max()
    result = []
    df_idx_set = set(df.index)
    for idx in range(max_idx + 1):
        if idx in df_idx_set:
            result.append({"df": df.loc[idx], "online": None})
        else:
            result.append({"df": None, "online": None})
    used_online_idx = set()
    used_df_idx = set()
    car_number_col = station_config["columns"].get("car_number", None)
    create_time_col = station_config["columns"].get("create_time", None)
    gas_num_col = station_config["columns"].get("gas_num", None)

    # step 对比1,完全一对一匹配数据
    for df_idx, df_row in df.iterrows():
        df_time = df_row[create_time_col]
        df_gas = float(df_row[gas_num_col])
        df_car = str(df_row[car_number_col]) if car_number_col else None
        for online_idx, online_row in online_df.iterrows():
            if online_idx in used_online_idx:
                continue
            online_time = online_row["create_time"]
            online_gas = float(online_row["gas_num"])
            online_car = str(online_row["car_number"]) if car_number_col else None
            if (
                abs((pd.to_datetime(df_time) - pd.to_datetime(online_time)).total_seconds()) <= diff_time
                and abs(df_gas - online_gas) < 0.01
            ):
                if car_number_col:
                    if df_car == online_car:
                        result[df_idx]["online"] = online_row
                        result[df_idx]["match_type"] = "1v1"
                        used_online_idx.add(online_idx)
                        used_df_idx.add(df_idx)
                        break
                else:
                    result[df_idx]["online"] = online_row
                    result[df_idx]["match_type"] = "1v1"
                    used_online_idx.add(online_idx)
                    used_df_idx.add(df_idx)
                    break

    # step 对比2: df的相邻多条记录gas_num相加一起对应online的一条记录（时间在2小时内）
    remaining_df = df.loc[~df.index.isin(used_df_idx)]
    remaining_online = online_df.loc[~online_df.index.isin(used_online_idx)]
    # 按时间排序
    remaining_df = remaining_df.sort_values(by=create_time_col)
    remaining_online = remaining_online.sort_values(by="create_time")
    # 遍历online剩余记录，尝试用df的多条记录相加匹配
    for online_idx, online_row in remaining_online.iterrows():
        online_time = pd.to_datetime(online_row["create_time"])
        online_gas = float(online_row["gas_num"])
        online_car = str(online_row["car_number"]) if car_number_col else None

        # 找到时间在2小时内且未使用的df记录
        candidates = []
        for df_idx, df_row in df.iterrows():
            if df_idx in used_df_idx:
                continue
            df_time = pd.to_datetime(df_row[create_time_col])
            if abs((df_time - online_time).total_seconds()) <= diff_time:
                if car_number_col:
                    df_car = str(df_row[car_number_col])
                    if df_car == online_car:
                        candidates.append((df_idx, df_row))
                else:
                    candidates.append((df_idx, df_row))

        # 尝试组合candidates的gas_num相加等于online_gas
        # 只考虑最多连续5条
        found = False
        for n in range(2, min(10, len(candidates) + 1)):
            for i in range(len(candidates) - n + 1):
                idxs = [candidates[j][0] for j in range(i, i + n)]
                rows = [candidates[j][1] for j in range(i, i + n)]
                total_gas = round(sum(float(row[gas_num_col]) for row in rows), 2)
                if abs(total_gas - online_gas) < 0.01:
                    # 匹配成功
                    for df_idx, df_row in zip(idxs, rows):
                        result[df_idx]["online"] = online_row
                        result[df_idx]["match_type"] = "nv1"
                        result[df_idx]["df_total_gas"] = total_gas
                        used_df_idx.add(df_idx)
                    used_online_idx.add(online_idx)
                    found = True
                    break
            if found:
                break

    # step 对比3: df的多条记录（不要求相邻）gas_num相加等于online的一条记录（时间在2小时内，自由组合）
    remaining_df = df.loc[~df.index.isin(used_df_idx)]
    remaining_online = online_df.loc[~online_df.index.isin(used_online_idx)]
    for online_idx, online_row in remaining_online.iterrows():
        online_time = pd.to_datetime(online_row["create_time"])
        online_gas = float(online_row["gas_num"])
        online_car = str(online_row["car_number"]) if car_number_col else None

        # 找到时间在2小时内且未使用的df记录
        candidates = []
        for df_idx, df_row in remaining_df.iterrows():
            df_time = pd.to_datetime(df_row[create_time_col])
            if abs((df_time - online_time).total_seconds()) <= diff_time:
                if car_number_col:
                    df_car = str(df_row[car_number_col])
                    if df_car == online_car:
                        candidates.append((df_idx, df_row))
                else:
                    candidates.append((df_idx, df_row))

        # 自由组合（最多5条），尝试组合gas_num相加等于online_gas
        found = False
        for n in range(2, min(10, len(candidates) + 1)):
            for combo in combinations(candidates, n):
                idxs = [item[0] for item in combo]
                rows = [item[1] for item in combo]
                total_gas = round(sum(float(row[gas_num_col]) for row in rows), 2)
                if abs(total_gas - online_gas) < 0.01:
                    # 匹配成功
                    for df_idx, df_row in zip(idxs, rows):
                        result[df_idx]["online"] = online_row
                        result[df_idx]["match_type"] = "nv1"
                        result[df_idx]["df_total_gas"] = total_gas
                        used_df_idx.add(df_idx)
                    used_online_idx.add(online_idx)
                    found = True
                    break
            if found:
                break

    # step 对比4: 如果设置了diff_num，就是允许相差diff_num以内的算疑似；
    if "diff_num" in station_config and station_config["diff_num"] is not None and station_config["diff_num"] > 0:
        # step 对比4.1 sus_1v1: 对剩下的df、 online进行1对1的比对（时间在2小时内），如果加气量相差在1以内的就设为sus_1v1
        remaining_df = df.loc[~df.index.isin(used_df_idx)]
        remaining_online = online_df.loc[~online_df.index.isin(used_online_idx)]
        for df_idx, df_row in remaining_df.iterrows():
            df_time = pd.to_datetime(df_row[create_time_col])
            df_gas = float(df_row[gas_num_col])
            df_car = str(df_row[car_number_col]) if car_number_col else None
            for online_idx, online_row in remaining_online.iterrows():
                online_time = pd.to_datetime(online_row["create_time"])
                online_gas = float(online_row["gas_num"])
                online_car = str(online_row["car_number"]) if car_number_col else None
                if abs((df_time - online_time).total_seconds()) <= diff_time:
                    if abs(df_gas - online_gas) <= station_config["diff_num"]:
                        if car_number_col:
                            if df_car == online_car:
                                result[df_idx]["online"] = online_row
                                result[df_idx]["match_type"] = "sus_1v1"
                                result[df_idx]["gas_diff"] = round(df_gas - online_gas, 2)
                                used_online_idx.add(online_idx)
                                used_df_idx.add(df_idx)
                                break
                        else:
                            result[df_idx]["online"] = online_row
                            result[df_idx]["match_type"] = "sus_1v1"
                            result[df_idx]["gas_diff"] = round(df_gas - online_gas, 2)
                            used_online_idx.add(online_idx)
                            used_df_idx.add(df_idx)
                            break

        # step 对比4.2 sus_nv1: 对剩下相邻的df、 online进行多对1的比对（时间在2小时内），如果加气量相差在1以内的就设为sus_nv1
        remaining_df = df.loc[~df.index.isin(used_df_idx)]
        remaining_online = online_df.loc[~online_df.index.isin(used_online_idx)]
        # 按时间排序
        remaining_df = remaining_df.sort_values(by=create_time_col)
        remaining_online = remaining_online.sort_values(by="create_time")
        # 遍历online剩余记录，尝试用df的多条记录相加匹配
        for online_idx, online_row in remaining_online.iterrows():
            online_time = pd.to_datetime(online_row["create_time"])
            online_gas = float(online_row["gas_num"])
            online_car = str(online_row["car_number"]) if car_number_col else None

            # 找到时间在2小时内且未使用的df记录
            candidates = []
            for df_idx, df_row in df.iterrows():
                if df_idx in used_df_idx:
                    continue
                df_time = pd.to_datetime(df_row[create_time_col])
                if abs((df_time - online_time).total_seconds()) <= diff_time:
                    if car_number_col:
                        df_car = str(df_row[car_number_col])
                        if df_car == online_car:
                            candidates.append((df_idx, df_row))
                    else:
                        candidates.append((df_idx, df_row))

            # 尝试组合candidates的gas_num相加等于online_gas
            # 只考虑最多连续5条
            found = False
            for n in range(2, min(10, len(candidates) + 1)):
                for i in range(len(candidates) - n + 1):
                    idxs = [candidates[j][0] for j in range(i, i + n)]
                    rows = [candidates[j][1] for j in range(i, i + n)]
                    total_gas = round(sum(float(row[gas_num_col]) for row in rows), 2)
                    if abs(total_gas - online_gas) < station_config["diff_num"]:
                        # 匹配成功
                        for df_idx, df_row in zip(idxs, rows):
                            result[df_idx]["online"] = online_row
                            result[df_idx]["match_type"] = "sus_nv1"
                            result[df_idx]["df_total_gas"] = total_gas
                            used_df_idx.add(df_idx)
                        used_online_idx.add(online_idx)
                        found = True
                        break
                if found:
                    break

        # step 对比3: df的多条记录（不要求相邻）gas_num相加等于online的一条记录（时间在2小时内，自由组合）
        remaining_df = df.loc[~df.index.isin(used_df_idx)]
        remaining_online = online_df.loc[~online_df.index.isin(used_online_idx)]
        for online_idx, online_row in remaining_online.iterrows():
            online_time = pd.to_datetime(online_row["create_time"])
            online_gas = float(online_row["gas_num"])
            online_car = str(online_row["car_number"]) if car_number_col else None

            # 找到时间在2小时内且未使用的df记录
            candidates = []
            for df_idx, df_row in remaining_df.iterrows():
                df_time = pd.to_datetime(df_row[create_time_col])
                if abs((df_time - online_time).total_seconds()) <= diff_time:
                    if car_number_col:
                        df_car = str(df_row[car_number_col])
                        if df_car == online_car:
                            candidates.append((df_idx, df_row))
                    else:
                        candidates.append((df_idx, df_row))

            # 自由组合（最多5条），尝试组合gas_num相加等于online_gas
            found = False
            for n in range(2, min(10, len(candidates) + 1)):
                for combo in combinations(candidates, n):
                    idxs = [item[0] for item in combo]
                    rows = [item[1] for item in combo]
                    total_gas = round(sum(float(row[gas_num_col]) for row in rows), 2)
                    if abs(total_gas - online_gas) < station_config["diff_num"]:
                        # 匹配成功
                        for df_idx, df_row in zip(idxs, rows):
                            result[df_idx]["online"] = online_row
                            result[df_idx]["match_type"] = "sus_nv1"
                            result[df_idx]["df_total_gas"] = total_gas
                            used_df_idx.add(df_idx)
                        used_online_idx.add(online_idx)
                        found = True
                        break
                if found:
                    break

    # step 对比5: 如果设置了车牌，就对加气量一致车牌不一致的进行一对一的比对设置为疑似1v1
    if car_number_col:
        # step 对比5.1 su1v1
        remaining_df = df.loc[~df.index.isin(used_df_idx)]
        remaining_online = online_df.loc[~online_df.index.isin(used_online_idx)]
        for df_idx, df_row in remaining_df.iterrows():
            df_time = pd.to_datetime(df_row[create_time_col])
            df_gas = float(df_row[gas_num_col])
            for online_idx, online_row in remaining_online.iterrows():
                online_time = pd.to_datetime(online_row["create_time"])
                online_gas = float(online_row["gas_num"])
                if abs((df_time - online_time).total_seconds()) <= diff_time:
                    if abs(df_gas - online_gas) <= 0.01:
                        result[df_idx]["online"] = online_row
                        result[df_idx]["match_type"] = "sus_1v1"
                        used_online_idx.add(online_idx)
                        used_df_idx.add(df_idx)
                        break

        # step 对比5.2 sunv1
        remaining_df = df.loc[~df.index.isin(used_df_idx)]
        remaining_online = online_df.loc[~online_df.index.isin(used_online_idx)]
        # 按时间排序
        remaining_df = remaining_df.sort_values(by=create_time_col)
        remaining_online = remaining_online.sort_values(by="create_time")
        # 遍历online剩余记录，尝试用df的多条记录相加匹配
        for online_idx, online_row in remaining_online.iterrows():
            online_time = pd.to_datetime(online_row["create_time"])
            online_gas = float(online_row["gas_num"])
            online_car = str(online_row["car_number"]) if car_number_col else None
            # 找到时间在2小时内且未使用的df记录
            candidates = []
            for df_idx, df_row in df.iterrows():
                if df_idx in used_df_idx:
                    continue
                df_time = pd.to_datetime(df_row[create_time_col])
                if abs((df_time - online_time).total_seconds()) <= diff_time:
                        candidates.append((df_idx, df_row))

            # 尝试组合candidates的gas_num相加等于online_gas
            # 只考虑最多连续5条
            found = False
            for n in range(2, min(10, len(candidates) + 1)):
                for i in range(len(candidates) - n + 1):
                    idxs = [candidates[j][0] for j in range(i, i + n)]
                    rows = [candidates[j][1] for j in range(i, i + n)]
                    total_gas = round(sum(float(row[gas_num_col]) for row in rows), 2)
                    if abs(total_gas - online_gas) < 0.01:
                        # 匹配成功
                        for df_idx, df_row in zip(idxs, rows):
                            result[df_idx]["online"] = online_row
                            result[df_idx]["match_type"] = "sus_nv1"
                            result[df_idx]["df_total_gas"] = total_gas
                            used_df_idx.add(df_idx)
                        used_online_idx.add(online_idx)
                        found = True
                        break
                if found:
                    break


    # step 对比END 按照online_row的create_time插入到result中相邻的online数据后面
    for online_idx, online_row in online_df.iterrows():
        if online_idx in used_online_idx:
            continue
        online_time = pd.to_datetime(online_row["create_time"])
        insert_pos = None
        # 遍历result，找到最大online create_time小于当前online_row的create_time的位置
        for idx, item in enumerate(result):
            if item["online"] is not None:
                item_time = pd.to_datetime(item["online"]["create_time"])
                if item_time < online_time:
                    insert_pos = idx
        # 如果找到插入点，则插入到该位置后面，否则插入到最前面
        if insert_pos is not None:
            result.insert(insert_pos + 1, {"df": None, "online": online_row})
        else:
            result.insert(0, {"df": None, "online": online_row})

    return result


# 输出结果
def create_report(match_result, station_config, file_path):
    tr = ""
    for i in match_result:
        for j in i:
            if j["df"] is not None and j["online"] is not None:
                tr_class = {"1v1": "match1", "nv1": "match2", "sus_1v1": "match3 diff", "sus_nv1": "match3 diff"}.get(
                    j.get("match_type"), ""
                )

                tr += f"""
                    <tr class='{tr_class}' data-id={j['online']['id']}><td><input type=checkbox /></td>
                    <td class=car_number>{j['df'][station_config["columns"]["car_number"]] if "car_number" in station_config['columns'] else ""}</td>
                    <td>{j['df'][station_config["columns"]["gas_price"]]}</td>
                    <td>{j['df'][station_config["columns"]["gas_num"]]}</td>
                    <td>{j['df'][station_config["columns"]["create_time"]]}</td>
                    <td class=gas_num>{j['df_total_gas'] if 'df_total_gas' in j else j['df'][station_config["columns"]["gas_num"]]}</td>
                    <td><input type=checkbox /></td>
                    <td>{j['online']['id']}</td>
                    <td class=car_number>{j['online']['car_number']}</td>
                    <td>{j['online']['gas_price']}</td>
                    <td class=gas_num>{j['online']['gas_num']}</td>
                    {'<td class=success>已结算</td>' if j['online']['status'] == 1 else '<td>未结算</td>'}
                    <td>{j['online']['create_time']}</td></tr>
                """
                # print(j["match_type"], j['df'][station_config["columns"]["gas_num"]], j["online"]["gas_num"])
                # print('-' * 20)
            elif j["df"] is not None:
                tr += f"""
                    <tr class='match0 df'><td><input type=checkbox /></td>
                    <td>{j['df'][station_config["columns"]["car_number"]] if "car_number" in station_config['columns'] else ""}</td>
                    <td>{j['df'][station_config["columns"]["gas_price"]]}</td>
                    <td>{j['df'][station_config["columns"]["gas_num"]]}</td>
                    <td>{j['df'][station_config["columns"]["create_time"]]}</td>
                    <td>{j['df_total_gas'] if 'df_total_gas' in j else ''}</td>
                    <td colspan=7></td></tr>
                """
                # print(j['df'][station_config["columns"]["gas_num"]], '-')
                # print('-' * 20)
            elif j["online"] is not None:
                tr += f"""
                    <tr class='match0 online'><td></td><td></td><td></td><td></td><td></td><td></td>
                    <td><input type=checkbox /></td>
                    <td>{j['online']['id']}</td>
                    <td>{j['online']['car_number']}</td>
                    <td>{j['online']['gas_price']}</td>
                    <td>{j['online']['gas_num']}</td>
                    {'<td class=success>已结算</td>' if j['online']['status'] == 1 else '<td>未结算</td>'}
                    <td>{j['online']['create_time']}</td></tr>
                """
                # print('-', j["online"]["gas_num"])
                # print('-' * 20)

    with open(file_path, mode="w", encoding="utf-8") as html_file:
        # 写入 HTML 文件头部
        html_file.write(
            f"""
    <link rel="stylesheet" href="main.css" />
    <div class="app">
        <div class="diff-table">
            <div class="action">
                <div class="filter"></div>
                <div>
                    <i class="icons-filter"></i>
                    <select>
                        <option>请选择气站</option>
                    </select>
                    <input type="datetime" style="width: 120px" placeholder="系统记录开始时间" />
                    <input type="datetime" style="width: 120px" placeholder="系统记录结束时间" />
                    <input type="text" style="width: 80px" placeholder="容错x公斤" />
                    <span>
                        <input type="checkbox" />
                        忽略时间
                    </span>
                    <button class="btn btn-dark" type="button">上传excel</button>
                </div>
            </div>
            <div class="table">
                <table>
                    <colgroup>
                        <col style="width: 20px; min-width: 20px; max-width: 20px" />
                        <col style="width: 80px; min-width: 80px; max-width: 80px" />
                        <col style="width: 50px; min-width: 50px; max-width: 50px" />
                        <col style="width: 70px; min-width: 70px; max-width: 70px" />
                        <col style="width: 140px; min-width: 140px; max-width: 140px" />
                        <col style="width: 70px; min-width: 70px; max-width: 70px" />
                        <col style="width: 20px; min-width: 20px; max-width: 20px" />
                        <col style="width: 80px; min-width: 80px; max-width: 80px" />
                        <col style="width: 90px; min-width: 90px; max-width: 90px" />
                        <col style="width: 50px; min-width: 50px; max-width: 50px" />
                        <col style="width: 70px; min-width: 70px; max-width: 70px" />
                        <col style="width: 70px; min-width: 70px; max-width: 70px" />
                        <col style="width: 140px; min-width: 140px; max-width: 140px" />
                    </colgroup>
                    <thead>
                        <tr>
                            <th>#</th>
                            <th>车牌</th>
                            <th>单价</th>
                            <th>加气量</th>
                            <th>时间</th>
                            <th>-</th>
                            <th>#</th>
                            <th>ID</th>
                            <th>车牌</th>
                            <th>单价</th>
                            <th>加气量</th>
                            <th>结算状态</th>
                            <th>时间</th>
                        </tr>
                    </thead>
                    <tbody>{tr}</tbody>
                    <tfoot>
                        <tr>
                            <th colspan="3">
                                <input type="checkbox" />
                                全选，已选：0条
                                <button class="btn btn-dark" type="button">提交异常</button>
                            </th>
                            <th>
                                <i>总</i>
                                <br />
                                <span class="df_total_gas_num"></span>
                            </th>
                            <th colspan="2">
                                <span class="df_total_gas"></span>
                                <br />
                                <i>条</i>
                            </th>
                            <th colspan="3">
                                <input type="checkbox" />
                                全选，已选：0条
                                <button class="btn btn-dark" type="button">核对</button>
                                <button class="btn btn-dark" type="button">提交异常</button>
                            </th>
                            <th>
                                <i>总</i>
                                <br />
                                <span class="online_total_gas_num"></span>
                            </th>
                            <th>
                                <span class="online_total_gas"></span>
                                <br />
                                <i>条</i>
                            </th>
                            <th colspan="2" style="text-align: right">
                                余额：0.00元 东莞新锋-赫森能源有限公司账单.xls
                                <button class="btn btn-light" type="button">上报错误</button>
                            </th>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>
    <script src="main.js"></script>
    """
        )


from collections import defaultdict
import json


def convert_numpy_types(obj):
    """递归转换 NumPy 类型为 Python 原生类型"""
    if isinstance(obj, (np.integer, np.int64, np.int32)):
        return int(obj)
    elif isinstance(obj, (np.floating, np.float64, np.float32)):
        return float(obj)
    elif isinstance(obj, np.ndarray):
        return obj.tolist()
    elif isinstance(obj, dict):
        return {k: convert_numpy_types(v) for k, v in obj.items()}
    elif isinstance(obj, (list, tuple)):
        return [convert_numpy_types(item) for item in obj]
    return obj


def create_json_result_v2(match_result, station_config, start_time, end_time):

    """
    将匹配结果转换为指定格式的JSON数据（正确处理多条Excel记录合并情况）
    """
    # 用于存储具有相同online记录的Excel数据
    online_groups = defaultdict(list)
    # 用于存储只有Excel数据的记录
    excel_only_records = []
    # 用于存储只有online数据的记录
    online_only_records = []

    for i in match_result:
        for j in i:
            if j["online"] is not None and j["df"] is not None:
                # 有匹配关系的记录，按online ID分组
                online_id = j['online']['id']
                online_groups[online_id].append(j)
            elif j["online"] is None and j["df"] is not None:
                # 只有Excel数据的记录
                excel_only_records.append(j)
            elif j["online"] is not None and j["df"] is None:
                # 只有online数据的记录
                online_only_records.append(j)

    result_list = []

    print(f"未匹配线上数据数量: {len(online_only_records)}")

    def get_reconciliation_status(item):
        """处理所有可能的对账状态"""
        # 处理 Pandas/NumPy 的 NaN
        if pd.isna(item):
            return "未核对"

        # 处理普通数值
        return "未核对" if item == 0 else "已核对"

    # 处理有匹配关系的记录组
    for online_id, group in online_groups.items():
        # 使用第一项的online数据作为基础
        first_item = group[0]
        item = {
            "excel": [],
            "excel_total_gas_num": 0.0,
            "original_id": first_item['online']['id'],
            "original_car_number": str(first_item['online']['car_number']),
            "original_gas_price": float(first_item['online']['gas_price']),
            "original_gas_num": float(first_item['online']['gas_num']),
            "original_gas_money": float(first_item['online']['gas_money']),
            "original_settlement_status": "已结算" if first_item['online']['status'] == 1 else "未结算",
            "original_supplier_reconciliation": get_reconciliation_status(first_item['online']['supplier_reconciliation']),
            "original_can_submit_exception_order": first_item['online']['canSubmitExceptionOrder'],
            "original_time": str(first_item['online']['create_time']),
            "original_actual_gas_num": float(first_item['online']['actualGasNum']),
            "original_supplier_reconciliation_remark": str(
                first_item['online'].get('supplier_reconciliation_remark') or ""),
            # 匹配类型标记字段
            "strict_matching": False,
            "many_for_one": False,
            "suspected": False,
            "original_not_excel_have": False,
            "original_have_excel_not": False
        }

        # 添加所有匹配的Excel记录并累加总加气量
        total_gas = 0.0
        has_nv1 = False
        has_sus = False

        for record in group:
            excel_item = {
                "excel_car_number": record['df'][station_config["columns"]["car_number"]] if "car_number" in station_config['columns'] else "",
                "excel_gas_price": record['df'][station_config["columns"]["gas_price"]] if "gas_price" in station_config['columns'] else "",
                "excel_gas_num": record['df'][station_config["columns"]["gas_num"]] if "gas_num" in station_config['columns'] else "",
                "excel_time": str(record['df'][station_config["columns"]["create_time"]]) if "create_time" in station_config['columns'] else "",
                "excel_station_id": record['df']["station_id"] if "station_id" in record['df'] else ""
            }
            item["excel"].append(excel_item)

            # 正确计算Excel总加气量
            try:
                # 对于多对一匹配(nv1, sus_nv1)，使用df_total_gas（这是多个excel记录的总和）
                # 对于一对一匹配(1v1, sus_1v1)，使用单个excel记录的加气量
                match_type = record.get("match_type", "")
                if match_type in ["nv1", "sus_nv1"] and 'df_total_gas' in record:
                    # 只有第一个记录需要计入总加气量，避免重复计算
                    if record == group[0]:  # 只有第一项包含df_total_gas的记录才计入
                        total_gas += float(record['df_total_gas'])
                else:
                    # 一对一匹配情况
                    total_gas += float(record['df'][station_config["columns"]["gas_num"]]) if "gas_num" in station_config['columns'] else 0
            except (ValueError, TypeError):
                pass

            # 检查匹配类型
            match_type = record.get("match_type", "")
            if match_type in ["nv1", "sus_nv1"]:
                has_nv1 = True
            if match_type in ["sus_1v1", "sus_nv1"]:
                has_sus = True

        item["excel_total_gas_num"] = round(total_gas, 2)

        # 设置匹配类型标记（只有一个为true）
        if has_nv1 and has_sus:
            item["suspected"] = True
        elif has_nv1:
            item["many_for_one"] = True
        elif has_sus:
            item["suspected"] = True
        else:
            item["strict_matching"] = True

        # 🔍 针对 strict_matching 和 many_for_one 再做单价一致性校验
        if item["strict_matching"] or item["many_for_one"]:
            prices = [
                round(float(excel.get("excel_gas_price")), 2)
                for excel in item["excel"]
                if excel.get("excel_gas_price") not in [None, ""]
            ]
            unique_prices = set(prices)

            original_price = item.get("original_gas_price")  # 取原始价格
            if len(unique_prices) > 1 or (original_price and any(p != original_price for p in prices)):
                # 发现价格不一致
                item["strict_matching"] = False
                item["many_for_one"] = False
                item["suspected"] = True

                # 🔖 打印详细日志
                logging.warning(
                    "价格不一致: customer_id=%s, original_price=%s, excel_prices=%s",
                    item.get("customer_id"),
                    original_price,
                    prices,
                )

        result_list.append(item)

    # 处理只有Excel数据的记录
    for record in excel_only_records:
        item = {
            "excel": [],
            "excel_total_gas_num": 0.0,
            "original_id": "",
            "original_car_number": "",
            "original_gas_price": "",
            "original_gas_num": "",
            "original_gas_money": "",
            "original_settlement_status": "",
            "original_supplier_reconciliation": "",
            "original_can_submit_exception_order": "",
            "original_time": "",
            "original_actual_gas_num": "",
            "original_supplier_reconciliation_remark": "",
            # 匹配类型标记字段
            "strict_matching": False,
            "many_for_one": False,
            "suspected": False,
            "original_not_excel_have": True,  # 只有Excel数据
            "original_have_excel_not": False
        }

        excel_item = {
            "excel_car_number": record['df'][station_config["columns"]["car_number"]] if "car_number" in station_config['columns'] else "",
            "excel_gas_price": record['df'][station_config["columns"]["gas_price"]] if "gas_price" in station_config['columns'] else "",
            "excel_gas_num": record['df'][station_config["columns"]["gas_num"]] if "gas_num" in station_config['columns'] else "",
            "excel_time": str(record['df'][station_config["columns"]["create_time"]]) if "create_time" in station_config['columns'] else "",
            "excel_station_id": record['df']["station_id"] if "station_id" in record['df'] else ""
        }
        item["excel"].append(excel_item)

        # 设置Excel总加气量为单条记录的加气量
        try:
            item["excel_total_gas_num"] = round(float(record['df'][station_config["columns"]["gas_num"]] if "gas_num" in station_config['columns'] else 0, 2))
        except (ValueError, TypeError):
            item["excel_total_gas_num"] = 0.0

        result_list.append(item)

    # 处理只有online数据的记录
    for record in online_only_records:
        item = {
            "excel": [],
            "excel_total_gas_num": 0.0,
            "original_id": record['online']['id'],
            "original_car_number": str(record['online']['car_number']),
            "original_gas_price": float(record['online']['gas_price']),
            "original_gas_num": float(record['online']['gas_num']),
            "original_gas_money": float(record['online']['gas_money']),
            "original_settlement_status": "已结算" if record['online']['status'] == 1 else "未结算",
            "original_supplier_reconciliation": get_reconciliation_status(record['online']['supplier_reconciliation']),
            "original_can_submit_exception_order": record['online']['canSubmitExceptionOrder'],
            "original_time": str(record['online']['create_time']),
            "original_actual_gas_num": float(record['online']['actualGasNum']),
            "original_supplier_reconciliation_remark": str(
                record['online'].get('supplier_reconciliation_remark') or ""),
            # 匹配类型标记字段
            "strict_matching": False,
            "many_for_one": False,
            "suspected": False,
            "original_not_excel_have": False,
            "original_have_excel_not": True  # 只有online数据
        }

        result_list.append(item)

    # 处理时间范围
    # 将对账开始时间提前1小时，结束时间延后1小时，扩大时间窗口以确保匹配的全面性
    start_dt = datetime.strptime(start_time, "%Y-%m-%d %H:%M:%S")
    end_dt = datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S")

    # 扩大时间范围（提前 1 小时、延后 1 小时）
    start_time = (start_dt + timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
    end_time = (end_dt - timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")

    # 从线上系统获取所有配置站点的加气数据
    balance_data_list = []

    # 判断是否是列表，多站点处理
    if isinstance(station_config['main_body_gas_station'], list):
        for station_id in station_config['main_body_gas_station']:
            balance_data = get_online_balance_data(station_id, start_time, end_time)
            if balance_data:
                balance_data_list.extend(balance_data)
    else:
        balance_data = get_online_balance_data(station_config['main_body_gas_station'], start_time, end_time)
        if balance_data:
            balance_data_list.extend(balance_data)

    # 用于存储每个站点的余额
    station_balance_map = {}

    # 计算总余额，同时记录每个站点的余额
    total_balance = 0
    for record in balance_data_list:
        station_id = record.get('id')  # 假设返回数据里有 station_id
        station_total = (
                record.get('balance', 0) +
                record.get('afterRechargeAmount', 0) +
                record.get('afterBeforeRechargeAmount', 0)
        )
        total_balance += station_total

        # 如果一个站点有多条记录，可以累加
        if station_id in station_balance_map:
            station_balance_map[station_id] += station_total
        else:
            station_balance_map[station_id] = station_total

    # 结果：
    # total_balance -> 所有站点总余额
    # station_balance_map -> {station_id: 该站点总余额}

    # total_balance 就是所有站点累加后的总余额

    # 组装map
    # {
    #   "result_list": result_list
    #   "total_balance": total_balance
    #   "stationSubjectId": station_config['main_body_gas_station']
    # }

    def sort_result_list(result_list):
        """
        对结果列表进行排序，优先按original_time从小到大排序，
        没有original_time的使用excel_time排序（如果excel_time存在）
        """

        def get_sort_key(item):
            # 优先使用original_time
            if item.get('original_time'):
                return item['original_time']

            # 其次尝试使用第一条excel记录的excel_time
            if item.get('excel') and len(item['excel']) > 0 and item['excel'][0].get('excel_time'):
                return item['excel'][0]['excel_time']

            # 都没有则返回空字符串，这些记录会排在最后
            return ""

        # 进行排序
        sorted_list = sorted(result_list, key=get_sort_key)

        return sorted_list

    return {
        "result_list": sort_result_list(convert_numpy_types(result_list)),
        "total_balance": total_balance,
        "station_subject_id": station_config['main_body_gas_station'],
        "station_ids": [station_config['ids']],
        "station_subject_balance_map": station_balance_map
    }



# 对比-v1
def match_data_v1(df, online_data, station_config):
    # 打印online_data的数量
    print(f"online_data数量：{len(online_data)}")
    print(f"station_config的配置：{station_config}")



    # 增加判断：如果online_data为空，直接返回只有Excel数据的结果
    if not online_data:
        df = df.copy()
        result = []
        for idx, row in df.iterrows():
            result.append({
                "df": row,
                "online": None
            })
        return result

    # 增加判断：如果df为空，直接返回只有online_data数据的结果
    if df is None or df.empty:
        online_data = online_data.copy()
        result = []
        for idx, row in enumerate(online_data):
            result.append({
                "df": None,
                "online": row
            })
        return result

    df = df.copy()
    online_df = pd.DataFrame(online_data)
    car_number_col = station_config["columns"].get("car_number", None)
    create_time_col = station_config["columns"].get("create_time", None)
    gas_num_col = station_config["columns"].get("gas_num", None)
    diff_num = station_config.get('diff_num', None)
    diff_time = 172800 if 'is_time_shuffled' in station_config and station_config['is_time_shuffled'] else 7200

    # 1. 预先将时间列转换为 datetime，避免重复转换
    if create_time_col is not None:
        df[create_time_col] = pd.to_datetime(df[create_time_col])
    if "create_time" in online_df.columns:
        online_df["create_time"] = pd.to_datetime(online_df["create_time"])

    # 2. 处理Excel 如果是多个气站并且Excel中的字段配置包含station_name，就需要设置Excel里面新增一个字段 station_id
    logging.info(f"设置station_id列后的列名: {list(df.columns)}")
    logging.info(f"设置station_id列后的数据量: {len(df)}")

    max_idx = df.index.max()
    result = []
    df_idx_set = set(df.index)
    for idx in range(max_idx + 1):
        if idx in df_idx_set:
            result.append({"df": df.loc[idx], "online": None})
        else:
            result.append({"df": None, "online": None})
    used_online_idx = set()
    used_df_idx = set()

    def check_time_match(t1, t2) -> bool:
        return abs((t1 - t2).total_seconds()) <= diff_time

    def check_car_match(df_car, online_car) -> bool:
        def clean(s):
            # 转成字符串，None → ""，去掉所有空白字符（包括前后、中间、制表符等）
            return re.sub(r"\s+", "", str(s or ""))

        return not car_number_col or (clean(df_car) == clean(online_car))

    def match_one_to_one(df_subset, online_subset, match_type, gas_tolerance, uncheck_car=False):
        for df_idx, df_row in df_subset.iterrows():
            df_time = df_row[create_time_col]
            df_gas = float(df_row[gas_num_col])
            df_car = str(df_row[car_number_col]) if car_number_col else None
            for online_idx, online_row in online_subset.iterrows():
                if online_idx in used_online_idx:
                    continue
                online_time = online_row["create_time"]
                online_gas = float(online_row["gas_num"])
                online_car = str(online_row["car_number"]) if car_number_col else None
                if (
                    check_time_match(df_time, online_time)
                    and abs(df_gas - online_gas) <= gas_tolerance
                    and (uncheck_car or check_car_match(df_car, online_car))
                ):
                    result[df_idx]["online"] = online_row
                    result[df_idx]["match_type"] = match_type
                    if gas_tolerance > 0.01:
                        result[df_idx]["gas_diff"] = round(df_gas - online_gas, 2)
                    used_online_idx.add(online_idx)
                    used_df_idx.add(df_idx)
                    break

    def match_many_to_one(df_subset, online_subset, match_type, gas_tolerance, uncheck_car=False, use_combinations=False):
        logging.info(f"匹配类型: {match_type} 匹配线上数量: {len(online_subset)} 匹配Excel数量: {len(df_subset)}")
        for online_idx, online_row in online_subset.iterrows():
            online_time = online_row["create_time"]
            online_gas = float(online_row["gas_num"])
            online_car = str(online_row["car_number"]) if car_number_col else None
            # 2. 仅在时间窗口内选取候选项
            # 1. 获取所有离线记录的时间列
            df_time_arr = df_subset[create_time_col]
            # 2. 创建时间掩码：筛选出在时间窗口内的记录
            time_mask = (df_time_arr - online_time).abs().dt.total_seconds() <= diff_time
            # 3. 获取候选记录：既在时间窗口内，又未被使用过的记录
            candidates_df = df_subset[time_mask & (~df_subset.index.isin(used_df_idx))]

            candidates = []
            for df_idx, df_row in candidates_df.iterrows():
                df_car = str(df_row[car_number_col]) if car_number_col else None
                if uncheck_car or check_car_match(df_car, online_car):
                    candidates.append((df_idx, df_row))
            found = False
            # 3. 限制组合枚举数量最多为10项，防止爆炸
            max_combo = 10
            # 判断 如果候选记录数量小于等于30那么max_combo = 10 如果候选记录数大于30 max_combo改为6 候选数过大 会导致计算爆炸
            if len(candidates) <= 30:
                max_combo = 10
            else:
                logging.warning(f"候选记录数: {len(candidates)}")
                max_combo = 6
            min_combo = 2
            max_n = min(max_combo, len(candidates))
            if max_n < min_combo:
                continue
            for n in range(min_combo, max_n + 1):
                if use_combinations:
                    for combo in combinations(candidates, n):
                        idxs = [item[0] for item in combo]
                        rows = [item[1] for item in combo]
                        total_gas = round(sum(float(row[gas_num_col]) for row in rows), 2)
                        if abs(total_gas - online_gas) <= gas_tolerance:
                            for df_idx, df_row in zip(idxs, rows):
                                result[df_idx]["online"] = online_row
                                result[df_idx]["match_type"] = match_type
                                result[df_idx]["df_total_gas"] = total_gas
                                used_df_idx.add(df_idx)
                            used_online_idx.add(online_idx)
                            found = True
                            break
                else:
                    for i in range(len(candidates) - n + 1):
                        idxs = [candidates[j][0] for j in range(i, i + n)]
                        rows = [candidates[j][1] for j in range(i, i + n)]
                        total_gas = round(sum(float(row[gas_num_col]) for row in rows), 2)
                        if abs(total_gas - online_gas) <= gas_tolerance:
                            for df_idx, df_row in zip(idxs, rows):
                                result[df_idx]["online"] = online_row
                                result[df_idx]["match_type"] = match_type
                                result[df_idx]["df_total_gas"] = total_gas
                                used_df_idx.add(df_idx)
                            used_online_idx.add(online_idx)
                            found = True
                            break
                if found:
                    break

    def find_all_combinations_branch_bound(candidates, n, online_gas, gas_tolerance):
        print(candidates)
        """
        回溯 + 剪枝算法
        - candidates: [(index, row_dict)]
        - n: 组合数量
        - online_gas: 目标总值
        - gas_tolerance: 容差
        返回: [(idx_list, total_gas), ...]
        """
        # 按油量排序
        sorted_data = sorted(
            [(i, float(row[gas_num_col])) for i, row in candidates],  # row 是 pd.Series
            key=lambda x: x[1]
        )

        indices = [x[0] for x in sorted_data]
        gas_values = [x[1] for x in sorted_data]
        n_total = len(candidates)
        target_low = online_gas - gas_tolerance
        target_high = online_gas + gas_tolerance

        results = []

        def dfs(start, path, total):
            if len(path) == n:
                if target_low <= total <= target_high:
                    results.append((path[:], round(total, 2)))
                return

            if n_total - start < n - len(path):  # 剩余元素不足以凑够 n
                return

            for i in range(start, n_total):
                new_total = total + gas_values[i]

                # 剪枝：如果当前和加上最小可能的剩余组合 > 上限，或加上最大可能剩余组合 < 下限，直接跳过
                remaining = n - len(path) - 1
                min_remaining = sum(gas_values[i + 1:i + 1 + remaining]) if remaining > 0 else 0
                max_remaining = sum(gas_values[-remaining:]) if remaining > 0 else 0

                if new_total + min_remaining > target_high or new_total + max_remaining < target_low:
                    continue

                dfs(i + 1, path + [indices[i]], new_total)

        dfs(0, [], 0)
        return results
    # 4. 用更清晰的结构组织 match 调用，避免重复生成剩余数据
    def get_remaining_df():
        return df.loc[~df.index.isin(used_df_idx)].sort_values(by=create_time_col)
    def get_remaining_online():
        return online_df.loc[~online_df.index.isin(used_online_idx)].sort_values(by="create_time")

    # 第一阶段：精确一对一匹配
    match_one_to_one(df, online_df, "1v1", 0.00)
    # 第二阶段：精确多对一匹配（滑动窗口）
    match_many_to_one(get_remaining_df(), get_remaining_online(), "nv1", 0.00, use_combinations=False)
    # 第三阶段：精确多对一匹配（任意组合）
    match_many_to_one(get_remaining_df(), get_remaining_online(), "nv1", 0.00, use_combinations=True)
    # 第四阶段：可疑匹配（如果指定了 diff_num）
    if diff_num is not None and diff_num > 0:
        match_one_to_one(get_remaining_df(), get_remaining_online(), "sus_1v1", diff_num)
        match_many_to_one(get_remaining_df(), get_remaining_online(), "sus_nv1", diff_num, use_combinations=False)
        match_many_to_one(get_remaining_df(), get_remaining_online(), "sus_nv1", diff_num, use_combinations=True)
    # 第五阶段：无车牌号检查的可疑匹配（如果存在 car_number_col）
    if car_number_col:
        match_one_to_one(get_remaining_df(), get_remaining_online(), "sus_1v1", diff_num, uncheck_car=True)
        match_many_to_one(get_remaining_df(), get_remaining_online(), "sus_nv1", diff_num, uncheck_car=True, use_combinations=False)
        match_many_to_one(get_remaining_df(), get_remaining_online(), "sus_nv1", diff_num, uncheck_car=True, use_combinations=True)

    # 5. 延后统一插入未匹配online，先收集再排序插入
    unmatched_online = []
    for online_idx, online_row in online_df.iterrows():
        if online_idx not in used_online_idx:
            unmatched_online.append(online_row)
    # 按 create_time 排序
    unmatched_online = sorted(unmatched_online, key=lambda x: x["create_time"])
    for online_row in unmatched_online:
        online_time = online_row["create_time"]
        insert_pos = None
        for idx, item in enumerate(result):
            if item["online"] is not None:
                item_time = item["online"]["create_time"]
                if item_time < online_time:
                    insert_pos = idx
        if insert_pos is not None:
            result.insert(insert_pos + 1, {"df": None, "online": online_row})
        else:
            result.insert(0, {"df": None, "online": online_row})

    # 统计未匹配线上数据数量
    unmatched_count = len(unmatched_online)
    print(f"未匹配线上数据数量：{unmatched_count}")
    return result


def set_station_id_column(station_data, station_config):
    """
    为Excel读取的数据设置station_id列字段

    Args:
        station_data: Excel读取的数据DataFrame
        station_config: 站点配置信息

    Returns:
        DataFrame: 添加了station_id列的数据（已过滤无效gas_num记录）
    """

    # 第一步：根据station_config配置的gas_num列名进行过滤
    gas_num_col = station_config.get('columns', {}).get('gas_num')
    if gas_num_col and gas_num_col in station_data.columns:
        initial_count = len(station_data)
        # 转换为数值类型并过滤
        station_data = station_data[
            pd.to_numeric(station_data[gas_num_col], errors='coerce') > 0
            ].copy()
        filtered_count = len(station_data)
        logging.info(f"已过滤{gas_num_col}<=0的记录: {initial_count} → {filtered_count} 条")
    elif gas_num_col:
        logging.warning(f"配置的gas_num列名 '{gas_num_col}' 不存在于数据中，跳过过滤步骤")
    else:
        logging.warning("未在station_config中配置gas_num列名，跳过过滤步骤")

    logging.info(f"开始设置station_id列字段")
    logging.info(f"station_data行数: {len(station_data)}")
    logging.info(f"station_config配置: {station_config}")
    logging.info(f"station_config列配置: {station_config.get('columns', {})}")
    logging.info(f"是否包含station_name字段: {'station_name' in station_config.get('columns', {})}")
    logging.info(f"站点ID数量: {len(station_config.get('ids', []))}")
    logging.info(f"是否包含nms字段: {'nms' in station_config}")

    # 情况1: 如果有station_name字段且有多个站点，需要循环匹配
    if "station_name" in station_config["columns"] and len(station_config["ids"]) > 1 and "nms" in station_config:
        logging.info("匹配情况1: 有station_name字段且有多个站点")
        station_names = station_config["nms"]
        station_name_col = station_config["columns"]["station_name"]
        logging.info(f"station_names模式: {station_names}")
        logging.info(f"station_name列名: {station_name_col}")

        # 为每条记录初始化station_id为空
        station_data = station_data.copy()
        station_data['station_id'] = None
        logging.info("已初始化station_id列为空")

        # 循环匹配每个站点的关键字
        for idx, pattern in enumerate(station_names):
            logging.info(f"处理模式 {idx}: {pattern}")
            # 找到匹配当前模式的记录索引
            matched_mask = station_data[station_name_col].astype(str).str.match(pattern, na=False)
            matched_count = matched_mask.sum()
            logging.info(f"模式 {pattern} 匹配到 {matched_count} 条记录")

            # 为匹配的记录设置对应的station_id
            station_data.loc[matched_mask, 'station_id'] = station_config["ids"][idx]
            logging.info(f"为匹配记录设置station_id: {station_config['ids'][idx]}")

        logging.info(f"最终station_id分布:\n{station_data['station_id'].value_counts(dropna=False)}")
        return station_data

    # 情况2: 如果没有station_name字段且只有一个站点ID，直接全部赋值
    elif "station_name" not in station_config["columns"] and len(station_config["ids"]) == 1:
        logging.info("匹配情况2: 没有station_name字段且只有一个站点ID")
        station_data = station_data.copy()
        station_data['station_id'] = station_config["ids"][0]
        logging.info(f"为所有记录设置统一station_id: {station_config['ids'][0]}")
        return station_data

    # 情况3: 如果没有station_name字段但是有多个station_config["ids"]，不进行赋值
    else:
        logging.info("匹配情况3: 没有station_name字段但是有多个station_config['ids']，不进行赋值")
        logging.info(f"当前条件: station_name字段存在: {'station_name' in station_config.get('columns', {})}, "
                    f"站点ID数量: {len(station_config.get('ids', []))}")
        return station_data


def set_price_column(station_data: pd.DataFrame, station_config: dict) -> pd.DataFrame:
    """
    为Excel读取的数据设置单价列字段，用 money 除以 gas_num 设置为 "gas_price"，结果保留两位小数
    同时更新 station_config 配置
    """
    # 获取配置中的列名
    gas_num_col = station_config.get('columns', {}).get('gas_num')
    money_col = station_config.get('columns', {}).get('money')

    # 检查必要的列是否存在
    if gas_num_col not in station_data.columns or money_col not in station_data.columns:
        raise ValueError(f"必要的列 {gas_num_col} 或 {money_col} 在数据中不存在")

    station_data[money_col] = pd.to_numeric(station_data[money_col], errors='coerce')
    station_data[gas_num_col] = pd.to_numeric(station_data[gas_num_col], errors='coerce')

    # 计算单价，避免除零或空值
    station_data["gas_price"] = np.where(
        (station_data[gas_num_col].notna()) & (station_data[gas_num_col] != 0),
        station_data[money_col].abs() / station_data[gas_num_col],
        np.nan
    )

    # 四舍五入保留两位小数
    station_data["gas_price"] = station_data["gas_price"].round(2)

    # 更新 station_config，添加 gas_price 列配置
    if 'columns' not in station_config:
        station_config['columns'] = {}
    station_config['columns']['gas_price'] = 'gas_price'

    return station_data

def log_file_reconciliation(operation_type, user_id, user_name, file_name, data, query):
    """记录文件对比日志"""
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # 确保表存在
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS file_reconciliation_file_log (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            operation_type TEXT,
            user_id INTEGER,
            user_name TEXT,
            file_name TEXT,
            data TEXT,
            query TEXT,
            create_time DATE
        )
    """)

    # 确保参数都是字符串或数字
    file_name = str(file_name) if file_name is not None else ""
    user_name = str(user_name) if user_name is not None else ""
    data = json.dumps(data, ensure_ascii=False, default=str)
    query = json.dumps(query, ensure_ascii=False, default=str)

    # 插入日志
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    cursor.execute("""
        INSERT INTO file_reconciliation_file_log 
        (operation_type, user_id, user_name, file_name, data, query, create_time) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (operation_type, user_id, user_name, file_name, data, query, now_str))

    conn.commit()
    conn.close()


def init_db():

    # 如果文件已存在，不覆盖，只提示
    if os.path.exists(DB_FILE):
        print(f"数据库文件 {DB_FILE} 已存在 ✅")
    else:
        print(f"正在创建数据库文件 {DB_FILE} ...")

    # 连接数据库（不存在则自动创建）
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    # 建表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS file_reconciliation_file_log (
      id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
      operation_type TEXT,
      user_id INTEGER,
      user_name TEXT,
      file_name TEXT,
      data TEXT,
      create_time DATE
    );
    """)

    conn.commit()
    conn.close()
    print(f"✅ 数据库 {DB_FILE} 初始化完成，表 file_reconciliation_file_log 已生成")

